﻿Imports System.Data
Imports System.Data.SqlClient
Imports System.Math
Imports Conexao_liion

Partial Class CustoFaturaData
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        Dim valida As String = Session("logado")

        'lblMsg.Visible = False

        'Faz com que ao sair do aplicativo, limpe o buffer e não podendo entrar na página através do butão voltar do Browser.
        Response.Cache.SetCacheability(HttpCacheability.ServerAndNoCache)
        'Fim

        Response.Cache.SetExpires(DateTime.Now.AddSeconds(1))

        lblMsg.Text = ""

        ' Gets a value indicating whether the page is being loaded in response to a client postback,
        ' or if it is being loaded and accessed for the first time.
        If Not Page.IsPostBack Then

            Dim strConnection As String
            strConnection = "A"

            If (LoadHolding(strConnection) = True) Then

                FillDDL()

                Try
                    If (Session("strOperacao") = "I") Then
                        ddlSituacao.SelectedValue = 2

                        lblHolding.Text = Session("strHolding")
                        lblHolding.ToolTip = Session("strCodigoDaHolding")

                        ddlCompanhia.SelectedValue = Session("strCodigoDaCia").ToString
                        FillDDLEstabelecimento()
                        ddlEstabelecimento.SelectedValue = Session("strCodigoDoEstabelecimento").ToString
                        txtNumeroDocumento.Text = Session("strNumeroDocumento")

                    ElseIf (Session("strOperacao") = "C" Or Session("strOperacao") = "A" Or Session("strOperacao") = "E") Then ' Consultar | Alterar | Excluir
                        LoadData()
                    End If

                    If (Session("strOperacao") = "C") Then ' Consultar
                        btnGravar.Enabled = False
                    End If

                Catch ex As Exception
                    lblMsg.Text = ex.Message
                End Try

            End If

        End If

    End Sub

    Private Function LoadHolding(ByVal strConn As String) As Boolean

        ' CAMG Informática | Marcelo Barcelos | 26.06.2010 08h

        LoadHolding = False

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            Dim theQueryString As String = "SELECT CodigoDaHolding, NomeDaHolding" & _
                         " FROM Holding;"

            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString

            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()

                If (theSqlDataReader.Read() = True) Then
                    lblHolding.Text = theSqlDataReader(1)
                    lblHolding.ToolTip = theSqlDataReader(0)
                    LoadHolding = True
                Else
                    lblMsg.Text = "Holding não cadastrada no Sistema."
                End If
                theSqlDataReader.Close()
            Catch ex As Exception
                'theSqlDataReader.Close()
                lblMsg.Text = "Msg DB: " & ex.Message
            End Try

        End Using

    End Function

    Private Sub FillDDL()

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            Dim mydatatable As New DataTable
            Dim theQueryString As String

            theQueryString = "SELECT CodigoDaCia, NomeDaCia" & _
                     " FROM Companhia" & _
                     " WHERE CodigoDaHolding = '" & lblHolding.ToolTip & "'" & _
                     " ORDER BY CodigoDaCia;"

            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString

            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()

                mydatatable.Load(theSqlDataReader)
                ddlCompanhia.DataTextField = "NomeDaCia"
                ddlCompanhia.DataValueField = "CodigoDaCia"
                ddlCompanhia.DataSource = mydatatable
                ddlCompanhia.DataBind()

                ddlCompanhia.Items.Insert(0, "")

                theSqlDataReader.Close()
            Catch ex As Exception
                'theSqlDataReader.Close()
                lblMsg.Text = "Msg DB: " & ex.Message
            End Try

        End Using

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            Try
                Dim mydatatable As New DataTable
                Dim theQueryString As String

                theQueryString = "SELECT MesAnoDeCompetencia," & _
                                 " LEFT(MesAnoDeCompetencia,2) + '/' + RIGHT(MesAnoDeCompetencia,4) AS Competencia" & _
                                 " FROM Competencia" & _
                                 " ORDER BY RIGHT(MesAnoDeCompetencia,4) + LEFT(MesAnoDeCompetencia,2) DESC;"

                Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
                theSqlCommand.CommandText = theQueryString

                theSqlConnection.Open()

                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()

                mydatatable.Load(theSqlDataReader)

                ddlCompetencia.DataTextField = "Competencia"
                ddlCompetencia.DataValueField = "MesAnoDeCompetencia"
                ddlCompetencia.DataSource = mydatatable
                ddlCompetencia.DataBind()

                ddlCompetencia.Items.Insert(0, "")

                theSqlDataReader.Close()
            Catch ex As Exception
                lblMsg.Text = "FillDDL: " & ex.Message
            End Try

        End Using

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            Dim mydatatable As New DataTable
            Dim theQueryString As String

            theQueryString = "SELECT CodSituacaoFatura, DscSituacaoFatura" & _
                 " FROM SituacaoFatura" & _
                 " ORDER BY CodSituacaoFatura;"

            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString

            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()

                mydatatable.Load(theSqlDataReader)
                ddlSituacao.DataTextField = "DscSituacaoFatura"
                ddlSituacao.DataValueField = "CodSituacaoFatura"
                ddlSituacao.DataSource = mydatatable
                ddlSituacao.DataBind()

                ddlSituacao.Items.Insert(0, "")

                theSqlDataReader.Close()
            Catch ex As Exception
                'theSqlDataReader.Close()
                lblMsg.Text = "Msg DB: " & ex.Message
            End Try

        End Using


        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            Dim mydatatable As New DataTable
            Dim theQueryString As String

            theQueryString = "SELECT CodFornecedor, CodFornecedor + ' - ' + RazaoSocialFornecedor AS RazaoSocialFornecedor" & _
                         " FROM Fornecedor" & _
                         " ORDER BY RazaoSocialFornecedor;"

            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString

            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()

                mydatatable.Load(theSqlDataReader)
                ddlFornecedor.DataTextField = "RazaoSocialFornecedor"
                ddlFornecedor.DataValueField = "CodFornecedor"
                ddlFornecedor.DataSource = mydatatable
                ddlFornecedor.DataBind()

                ddlFornecedor.Items.Insert(0, "")

                theSqlDataReader.Close()
            Catch ex As Exception
                'theSqlDataReader.Close()
                lblMsg.Text = "Msg DB: " & ex.Message
            End Try

        End Using


        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            Dim mydatatable As New DataTable
            Dim theQueryString As String

            theQueryString = "SELECT CodigoDaMoeda, SiglaDaMoeda + ' - ' + NomeDaMoeda AS SiglaDaMoeda" & _
                     " FROM Moeda" & _
                     " ORDER BY SiglaDaMoeda;"

            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString

            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()

                mydatatable.Load(theSqlDataReader)
                ddlMoeda.DataTextField = "SiglaDaMoeda"
                ddlMoeda.DataValueField = "CodigoDaMoeda"
                ddlMoeda.DataSource = mydatatable
                ddlMoeda.DataBind()

                ddlMoeda.Items.Insert(0, "")

                theSqlDataReader.Close()
            Catch ex As Exception
                'theSqlDataReader.Close()
                lblMsg.Text = "Msg DB: " & ex.Message
            End Try

        End Using


        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            Dim mydatatable As New DataTable
            Dim theQueryString As String

            theQueryString = "SELECT CodImportador, CodImportador + ' - ' + NomeFantasia AS NomeFantasia" & _
                         " FROM Importador" & _
                         " ORDER BY CodImportador;"

            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString

            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()

                mydatatable.Load(theSqlDataReader)
                ddlImportador.DataTextField = "NomeFantasia"
                ddlImportador.DataValueField = "CodImportador"
                ddlImportador.DataSource = mydatatable
                ddlImportador.DataBind()

                ddlImportador.Items.Insert(0, "")

                theSqlDataReader.Close()
            Catch ex As Exception
                'theSqlDataReader.Close()
                lblMsg.Text = "Msg DB: " & ex.Message
            End Try

        End Using

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            Dim mydatatable As New DataTable
            Dim theQueryString As String

            theQueryString = "SELECT CodIncoterm, CodIncoterm + ' - ' + DscIncoterm AS Incoterm" & _
                         " FROM Incoterm" & _
                         " ORDER BY DscIncoterm;"

            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString

            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()

                mydatatable.Load(theSqlDataReader)
                ddlIncoterm.DataTextField = "Incoterm"
                ddlIncoterm.DataValueField = "CodIncoterm"
                ddlIncoterm.DataSource = mydatatable
                ddlIncoterm.DataBind()

                ddlIncoterm.Items.Insert(0, "")

                theSqlDataReader.Close()
            Catch ex As Exception
                'theSqlDataReader.Close()
                lblMsg.Text = "Msg DB: " & ex.Message
            End Try

        End Using

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            Dim mydatatable As New DataTable
            Dim theQueryString As String

            theQueryString = "SELECT CodCondicaoPagamento, DscCondicaoPagamento" & _
                         " FROM CondicaoPagamento" & _
                         " ORDER BY NumeroDias;"

            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString

            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()

                mydatatable.Load(theSqlDataReader)
                ddlCondicaoPagamento.DataTextField = "DscCondicaoPagamento"
                ddlCondicaoPagamento.DataValueField = "CodCondicaoPagamento"
                ddlCondicaoPagamento.DataSource = mydatatable
                ddlCondicaoPagamento.DataBind()

                ddlCondicaoPagamento.Items.Insert(0, "")


                theSqlDataReader.Close()
            Catch ex As Exception
                'theSqlDataReader.Close()
                lblMsg.Text = "Msg DB: " & ex.Message
            End Try

        End Using

    End Sub

    Private Sub LoadData()

        'Dim br As New CultureInfo("pt-BR")

        Dim intQtdeItems As Integer = 0
        Dim strFatura As String

        ddlCompanhia.Enabled = False
        ddlEstabelecimento.Enabled = False

        If (Session("strOperacao").ToString = "C" Or Session("strOperacao").ToString = "E") Then ' Consultar | Excluir
            txtFatura.Enabled = False
            ddlCompetencia.Enabled = False
            txtDataFatura.Enabled = False
            txtQtdeItems.Enabled = False
            txtValorTotalItems.Enabled = False
            txtValorDespesasEmbarque.Enabled = False
            txtValorTotalFatura.Enabled = False
            ddlFornecedor.Enabled = False
            ddlMoeda.Enabled = False
            ddlImportador.Enabled = False
        End If

        lblHolding.Text = Session("strHolding")
        lblHolding.ToolTip = Session("strCodigoDaHolding")

        ddlCompanhia.SelectedValue = Session("strCodigoDaCia").ToString
        FillDDLEstabelecimento()
        ddlEstabelecimento.SelectedValue = Session("strCodigoDoEstabelecimento").ToString
        txtNumeroDocumento.Text = Session("strNumeroDocumento")

        txtFatura.Text = Session("strFatura").ToString


        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            Dim theQueryString As String
            theQueryString = "Select Fatura " & _
                        " FROM custofaturaheader " & _
                        " WHERE CodigoDaHolding = '" & lblHolding.ToolTip & "' AND " & _
                        " CodigoDaCia = '" & Session("strCodigoDaCia").ToString & "' AND " & _
                        " CodigoDoEstabelecimento = '" & Session("strCodigoDoEstabelecimento").ToString & "' AND " & _
                        " NumeroDocumento = '" & txtNumeroDocumento.Text & "' AND Fatura = '" & txtFatura.Text & "'"

            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString

            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()

                If (theSqlDataReader.Read()) Then
                    strFatura = theSqlDataReader!Fatura
                    txtFatura.Text = theSqlDataReader!Fatura
                    Session.Add("strFatura", HttpUtility.HtmlDecode(theSqlDataReader!Fatura))
                    txtNumeroDocumento.Enabled = False
                    btnItem.Enabled = True
                    If (Session("strOperacao") = "E") Then
                        Session.Add("strOperacao", "E")
                    Else
                        Session.Add("strOperacao", "A")
                    End If

                Else
                    Session.Add("strOperacao", "I") ' Vazio
                    Exit Sub
                End If

                theSqlDataReader.Close()
            Catch ex As Exception
                'theSqlDataReader.Close()
                lblMsg.Text = "Msg DB: " & ex.Message
            End Try

        End Using


        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            Dim theQueryString As String = "SELECT" & _
                         " QtdeItems," & _
                         " MesAnoDeCompetencia," & _
                         " DataFatura," & _
                         " ROUND(ValorTotalItems,2) AS ValorTotalItems," & _
                         " ROUND(ValorDespesasEmbarque,2) AS ValorDespesasEmbarque," & _
                         " ROUND(ValorTotalFatura,2) AS ValorTotalFatura," & _
                         " CodSituacaoFatura," & _
                         " CodFornecedor," & _
                         " CodigoDaMoeda," & _
                         " CodImportador," & _
                         " CodIncoterm," & _
                         " CodCondicaoPagamento" & _
                         " FROM custofaturaheader" & _
                         " WHERE CodigoDaHolding = '" & lblHolding.ToolTip & "'" & _
                         " AND CodigoDaCia = '" & ddlCompanhia.SelectedValue & "'" & _
                         " AND CodigoDoEstabelecimento = '" & ddlEstabelecimento.SelectedValue & "'" & _
                         " AND Fatura = '" & UCase(Trim(strFatura)) & "';"

            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString

            Try

                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()

                Do While theSqlDataReader.Read()

                    If Not (IsDBNull(theSqlDataReader!MesAnoDeCompetencia) = True) Then
                        ddlCompetencia.SelectedValue = theSqlDataReader!MesAnoDeCompetencia
                    End If

                    txtDataFatura.Text = Format(CDate(theSqlDataReader!DataFatura.ToString), "dd/MM/yyyy")

                    txtQtdeItems.Text = 0

                    If Not (IsDBNull(theSqlDataReader!QtdeItems) = True) Then
                        txtQtdeItems.Text = theSqlDataReader!QtdeItems
                    End If

                    txtValorTotalItems.Text = theSqlDataReader!ValorTotalItems
                    'txtValorTotalItems.Text = Format((theSqlDataReader!ValorTotalItems("n", br)))
                    'txtValorTotalItems.Text = FormatNumber(theSqlDataReader!ValorTotalItems, 2, -1, -1, -2)
                    txtValorDespesasEmbarque.Text = theSqlDataReader!ValorDespesasEmbarque
                    txtValorTotalFatura.Text = theSqlDataReader!ValorTotalFatura

                    ddlSituacao.SelectedValue = theSqlDataReader!CodSituacaoFatura
                    ddlFornecedor.SelectedValue = theSqlDataReader!CodFornecedor
                    ddlMoeda.SelectedValue = theSqlDataReader!CodigoDaMoeda
                    ddlImportador.SelectedValue = theSqlDataReader!CodImportador
                    ddlIncoterm.SelectedValue = theSqlDataReader!CodIncoterm
                    ddlCondicaoPagamento.SelectedValue = theSqlDataReader!CodCondicaoPagamento

                Loop

                theSqlDataReader.Close()
            Catch ex As Exception
                'theSqlDataReader.Close()
                lblMsg.Text = "Msg DB: " & ex.Message
            End Try

        End Using


        If (Session("strOperacao") = "A") Then ' Alterar

            Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

                Dim theQueryString As String = "SELECT" & _
                             " COUNT(CodItem) AS QtdeItems" & _
                             " FROM custofaturadetail" & _
                             " WHERE CodigoDaHolding = '" & lblHolding.ToolTip & "'" & _
                             " AND CodigoDaCia = '" & ddlCompanhia.SelectedValue & "'" & _
                             " AND CodigoDoEstabelecimento = '" & ddlEstabelecimento.SelectedValue & "'" & _
                             " AND Fatura = '" & UCase(Trim(txtFatura.Text)) & "';"

                Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
                theSqlCommand.CommandText = theQueryString

                Try

                    theSqlConnection.Open()
                    Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()

                    Do While theSqlDataReader.Read()
                        intQtdeItems = theSqlDataReader!QtdeItems
                    Loop

                    theSqlDataReader.Close()
                Catch ex As Exception
                    'theSqlDataReader.Close()
                    lblMsg.Text = "Msg DB: " & ex.Message
                End Try

            End Using

            If (intQtdeItems <> 0) Then
                txtQtdeItems.Enabled = False
                txtValorTotalItems.Enabled = False
                txtValorDespesasEmbarque.Enabled = False
                txtValorTotalFatura.Enabled = False
            End If

        End If

    End Sub

    Protected Sub ddlCompanhia_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ddlCompanhia.SelectedIndexChanged

        FillDDLEstabelecimento()

    End Sub

    Private Sub FillDDLEstabelecimento()

        ' CAMG Informática | Marcelo Barcelos | 05.01.2010 16h45

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            Dim mydatatable As New DataTable
            Dim theQueryString As String

            theQueryString = "SELECT CodigoDoEstabelecimento, CodigoDoEstabelecimento + ' - ' + RazaoSocialDoEstabelecimento AS Estabelecimento" & _
                     " FROM Estabelecimento" & _
                     " WHERE CodigoDaHolding = '" & lblHolding.ToolTip & "'" & _
                     " AND CodigoDaCia = '" & ddlCompanhia.SelectedValue & "'" & _
                     " ORDER BY CodigoDoEstabelecimento;"

            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString

            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()

                mydatatable.Load(theSqlDataReader)
                ddlEstabelecimento.DataTextField = "Estabelecimento"
                ddlEstabelecimento.DataValueField = "CodigoDoEstabelecimento"
                ddlEstabelecimento.DataSource = mydatatable
                ddlEstabelecimento.DataBind()

                ddlEstabelecimento.Items.Insert(0, "")
                theSqlDataReader.Close()
            Catch ex As Exception
                'theSqlDataReader.Close()
                lblMsg.Text = "Msg DB: " & ex.Message
            End Try

        End Using


    End Sub

    Protected Sub ddlFornecedor_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ddlFornecedor.SelectedIndexChanged

        LoadDataFornecedor()

    End Sub

    Private Sub LoadDataFornecedor()

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            ddlMoeda.SelectedIndex = -1

            Dim theQueryString As String
            theQueryString = "SELECT CodigoDaMoeda" & _
                     " FROM Fornecedor" & _
                     " WHERE CodFornecedor = '" & ddlFornecedor.SelectedValue & "';"

            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString

            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()

                If (theSqlDataReader.HasRows = True) Then

                    Do While theSqlDataReader.Read()

                        If Not (IsDBNull(theSqlDataReader(0)) = True) Then
                            ddlMoeda.SelectedValue = theSqlDataReader(0)
                        End If

                    Loop
                End If

                theSqlDataReader.Close()
            Catch ex As Exception
                'theSqlDataReader.Close()
                lblMsg.Text = "Msg DB: " & ex.Message
            End Try

        End Using

    End Sub

    Protected Sub btnGravar_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnGravar.Click

        Dim ConnectionTest As String
        ConnectionTest = "A"

        lblMsg.Text = ""

        If (Session("strOperacao") = "I") Then ' Incluir

            If Not (DataValid() = True) Then
                Exit Sub
            End If

            If (CodeExist(ConnectionTest) = True) Then
                Exit Sub
            End If

            If Not (AddRecord(ConnectionTest) = True) Then
                Exit Sub
            End If

        ElseIf (Session("strOperacao") = "A") Then ' Alterar

            If Not (DataValid() = True) Then
                Exit Sub
            End If

            If Not (EditRecord(ConnectionTest) = True) Then
                Exit Sub
            End If

        ElseIf (Session("strOperacao") = "E") Then ' Excluir

            If (VerifyData(ConnectionTest) = True) Then
                Exit Sub
            End If

            If Not (DeleteRecord(ConnectionTest) = True) Then
                Exit Sub
            End If

        End If

        lblMsg.ForeColor = Drawing.Color.Green
        lblMsg.Text = "Operação realizada com sucesso. Clique no botão 'OK'"

        btnGravar.Enabled = False
        btnItem.Enabled = True
        btnVoltar.Text = "OK"

    End Sub

    Private Function DataValid() As Boolean

        Dim dblSomaItemsDespesas As Double = 0
        Dim dblValorTotalFatura As Double = 0

        DataValid = False

        If (ddlCompanhia.Text = "") Then
            lblMsg.Text = "Companhia deve ser selecionada."
            Exit Function
        End If

        If (ddlEstabelecimento.Text = "") Then
            lblMsg.Text = "Estabelecimento deve ser selecionado."
            Exit Function
        End If

        If (Trim(txtFatura.Text) = "") Then
            lblMsg.Text = "Fatura deve ser preenchida."
            Exit Function
        End If

        If (ddlCompetencia.Text = "") Then
            lblMsg.Text = "Competência deve ser selecionada."
            Exit Function
        End If

        If (Trim(txtDataFatura.Text) = "") Then
            lblMsg.Text = "Data da Fatura deve ser preenchida."
            Exit Function
        End If

        If Not IsDate(txtDataFatura.Text) = True Then
            lblMsg.Text = "Data da Fatura inválida."
            Exit Function
        End If

        If (Right(ddlCompetencia.SelectedValue, 4) < Right(Trim(txtDataFatura.Text), 4)) Then
            lblMsg.Text = "Mês/Ano da Data da Fatura deve ser anterior ou igual à Competência selecionada."
            Exit Function
        End If

        ' Qtde. de items da Fatura
        If (Trim(txtQtdeItems.Text) = "") Then
            lblMsg.Text = "Qtde. de items da Fatura deve ser preenchida."
            Exit Function
        End If

        If Not IsNumeric(txtQtdeItems.Text) = True Then
            lblMsg.Text = "Qtde. de items da Fatura deve ser numérico."
            Exit Function
        End If

        If (Trim(txtQtdeItems.Text) <= 0) Then
            lblMsg.Text = "Qtde. de items da Fatura deve ser maior que zero."
            Exit Function
        End If

        ' Valor total dos items
        If (Trim(txtValorTotalItems.Text) = "") Then
            lblMsg.Text = "Valor total dos items deve ser preenchido."
            Exit Function
        End If

        If Not IsNumeric(txtValorTotalItems.Text) = True Then
            lblMsg.Text = "Valor total dos items deve ser numérico."
            Exit Function
        End If

        If (Trim(txtValorTotalItems.Text) <= 0) Then
            lblMsg.Text = "Valor total dos items deve ser maior que zero."
            Exit Function
        End If

        ' Valor das despesas de embarque
        If (Trim(txtValorDespesasEmbarque.Text) = "") Then
            lblMsg.Text = "Valor das despesas de embarque deve ser preenchido."
            Exit Function
        End If

        If Not IsNumeric(txtValorDespesasEmbarque.Text) = True Then
            lblMsg.Text = "Valor das despesas de embarque deve ser numérico."
            Exit Function
        End If

        If (Trim(txtValorDespesasEmbarque.Text) < 0) Then
            lblMsg.Text = "Valor das despesas de embarque deve ser igual ou maior que zero."
            Exit Function
        End If

        ' Valor total da Fatura
        If (Trim(txtValorTotalFatura.Text) = "") Then
            lblMsg.Text = "Valor total da Fatura deve ser preenchido."
            Exit Function
        End If

        If Not IsNumeric(txtValorTotalFatura.Text) = True Then
            lblMsg.Text = "Valor total da Fatura deve ser numérico."
            Exit Function
        End If

        If (Trim(txtValorTotalFatura.Text) <= 0) Then
            lblMsg.Text = "Valor total da Fatura deve ser maior que zero."
            Exit Function
        End If

        dblSomaItemsDespesas = (CDbl(txtValorTotalItems.Text) + CDbl(txtValorDespesasEmbarque.Text))
        dblSomaItemsDespesas = Round(dblSomaItemsDespesas, 2)
        dblValorTotalFatura = CDbl(txtValorTotalFatura.Text)

        If Not (dblSomaItemsDespesas = dblValorTotalFatura) Then
            lblMsg.Text = "Valor total da Fatura (" & dblValorTotalFatura & ") não confere com a soma dos totais dos items e despesas (" & dblSomaItemsDespesas & ")."
            Exit Function
        End If

        If (ddlFornecedor.Text = "") Then
            lblMsg.Text = "Fornecedor deve ser selecionado."
            Exit Function
        End If

        If (ddlMoeda.Text = "") Then
            lblMsg.Text = "Moeda deve ser selecionada."
            Exit Function
        End If

        If (ddlImportador.Text = "") Then
            lblMsg.Text = "Importador deve ser selecionado."
            Exit Function
        End If

        DataValid = True

    End Function

    Private Function CodeExist(ByVal strConn As String) As Boolean

        CodeExist = False

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            Dim theQueryString As String
            theQueryString = "SELECT Fatura" & _
                     " FROM custofaturaheader" & _
                     " WHERE CodigoDaHolding = '" & lblHolding.ToolTip & "'" & _
                     " AND CodigoDaCia = '" & ddlCompanhia.SelectedValue & "'" & _
                     " AND CodigoDoEstabelecimento = '" & ddlEstabelecimento.SelectedValue & "'" & _
                     " AND Fatura = '" & UCase(Trim(txtFatura.Text)) & "';"
            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString

            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()

                If (theSqlDataReader.Read()) Then
                    CodeExist = True
                    lblMsg.Text = "Fatura " & txtFatura.Text & " já existe no Sistema."
                End If
                theSqlDataReader.Close()
            Catch ex As Exception
                CodeExist = True
                'theSqlDataReader.Connection.Close()
                lblMsg.Text = "Msg DB: " & ex.Message
            End Try

        End Using

    End Function

    Private Function AddRecord(ByVal strConn As String) As Boolean

        Dim strDataFatura As String
        Dim nDiaCreated As String
        Dim nMesCreated As String
        Dim nAnoCreated As String
        Dim nLastUpdated As String

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            'Dim dAnoVigente As String
            'Dim i As Long

            Dim theQueryString As String = "SELECT CONVERT(CHAR(10), GETDATE(), 103) AS DATADOSERVIDOR"
            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString
            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()
                Do While theSqlDataReader.Read()
                    Session("dDTAno") = Trim(theSqlDataReader(0))
                Loop
                theSqlDataReader.Close()
            Catch ex As Exception
                Console.WriteLine(ex.Message)
            End Try

        End Using

        nDiaCreated = Mid(Session("dDTAno"), 1, 2)
        nMesCreated = Mid(Session("dDTAno"), 4, 2)
        nAnoCreated = Mid(Session("dDTAno"), 7, 4)
        nLastUpdated = nDiaCreated & "/" & nMesCreated & "/" & nAnoCreated

        If (IsDate(nLastUpdated)) Then
            nLastUpdated = "'" & FormatMediumDate(nLastUpdated) & "'"
        Else
            nLastUpdated = "Null"
        End If

        AddRecord = False

        txtValorTotalItems.Text = Replace(txtValorTotalItems.Text, ",", ".")
        txtValorDespesasEmbarque.Text = Replace(txtValorDespesasEmbarque.Text, ",", ".")
        txtValorTotalFatura.Text = Replace(txtValorTotalFatura.Text, ",", ".")

        'strDataFatura = Format(CDate(txtDataFatura.Text), "yyyy-MM-dd HH:mm:ss")
        strDataFatura = "'" & FormatMediumDate(txtDataFatura.Text) & "'"

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            Dim theQueryString As String
            theQueryString = "INSERT INTO custofaturaheader" & _
                         " (" & _
                         " CodigoDaHolding," & _
                         " CodigoDaCia," & _
                         " CodigoDoEstabelecimento," & _
                         " Fatura," & _
                         " NumeroDocumento, " & _
                         " MesAnoDeCompetencia," & _
                         " DataFatura," & _
                         " QtdeItems," & _
                         " ValorTotalItems," & _
                         " ValorDespesasEmbarque," & _
                         " ValorTotalFatura," & _
                         " CodSituacaoFatura," & _
                         " CodFornecedor," & _
                         " CodigoDaMoeda," & _
                         " CodImportador," & _
                         " CodIncoterm," & _
                         " CodCondicaoPagamento," & _
                         " CreateUser," & _
                         " Created" & _
                         " )"

            theQueryString = theQueryString & _
                     " VALUES " & _
                     " (" & _
                     " '" & lblHolding.ToolTip & "'," & _
                     " '" & ddlCompanhia.SelectedValue & "'," & _
                     " '" & ddlEstabelecimento.SelectedValue & "'," & _
                     " '" & txtFatura.Text & "'," & _
                     " '" & txtNumeroDocumento.Text & "'," & _
                     " '" & ddlCompetencia.SelectedValue & "'," & _
                     " " & strDataFatura & "," & _
                     " " & txtQtdeItems.Text & "," & _
                     " " & txtValorTotalItems.Text & "," & _
                     " " & txtValorDespesasEmbarque.Text & "," & _
                     " " & txtValorTotalFatura.Text & "," & _
                     " 2," & _
                     " '" & ddlFornecedor.SelectedValue & "'," & _
                     " '" & ddlMoeda.SelectedValue & "'," & _
                     " '" & ddlImportador.SelectedValue & "'," & _
                     " '" & ddlIncoterm.SelectedValue & "'," & _
                     " " & ddlCondicaoPagamento.SelectedValue & "," & _
                     " '" & Session("sNM_USERNICKNAME").ToString & "'," & _
                     " " & nLastUpdated & "" & _
                     " );"

            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString
            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()
                theSqlDataReader.Close()
                AddRecord = True
            Catch ex As Exception
                'theSqlDataReader.Close()
                lblMsg.Text = "Msg DB: " & ex.Message
            End Try

        End Using

    End Function

    Private Function EditRecord(ByVal strConn As String) As Boolean

        Dim strDataFatura As String
        Dim nDiaCreated As String
        Dim nMesCreated As String
        Dim nAnoCreated As String
        Dim nLastUpdated As String

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            Try
                Dim theQueryString As String = "SELECT CONVERT(CHAR(10), GETDATE(), 103) AS DATADOSERVIDOR"
                Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
                theSqlCommand.CommandText = theQueryString

                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()
                Do While theSqlDataReader.Read()
                    Session("dDTAno") = Trim(theSqlDataReader(0))
                Loop
                theSqlDataReader.Close()
            Catch ex As Exception
                Console.WriteLine(ex.Message)
            End Try

        End Using

        nDiaCreated = Mid(Session("dDTAno"), 1, 2)
        nMesCreated = Mid(Session("dDTAno"), 4, 2)
        nAnoCreated = Mid(Session("dDTAno"), 7, 4)
        nLastUpdated = nDiaCreated & "/" & nMesCreated & "/" & nAnoCreated

        If (IsDate(nLastUpdated)) Then
            nLastUpdated = "'" & FormatMediumDate(nLastUpdated) & "'"
        Else
            nLastUpdated = "Null"
        End If

        EditRecord = False

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            Try
                txtValorTotalItems.Text = Replace(txtValorTotalItems.Text, ",", ".")
                txtValorDespesasEmbarque.Text = Replace(txtValorDespesasEmbarque.Text, ",", ".")
                txtValorTotalFatura.Text = Replace(txtValorTotalFatura.Text, ",", ".")

                'strDataFatura = Format(CDate(txtDataFatura.Text), "yyyy-MM-dd HH:mm:ss")
                strDataFatura = "'" & FormatMediumDate(txtDataFatura.Text) & "'"

                Dim theQueryString As String
                theQueryString = "UPDATE custofaturaheader SET" & _
                         " NumeroDocumento = '" & txtNumeroDocumento.Text & "', " & _
                         " MesAnoDeCompetencia = '" & ddlCompetencia.SelectedValue & "'," & _
                         " DataFatura = " & strDataFatura & "," & _
                         " QtdeItems = " & txtQtdeItems.Text & "," & _
                         " ValorTotalItems = " & txtValorTotalItems.Text & "," & _
                         " ValorDespesasEmbarque = " & txtValorDespesasEmbarque.Text & "," & _
                         " ValorTotalFatura = " & txtValorTotalFatura.Text & "," & _
                         " CodFornecedor = '" & ddlFornecedor.SelectedValue & "'," & _
                         " CodigoDaMoeda = '" & ddlMoeda.SelectedValue & "'," & _
                         " CodImportador = '" & ddlImportador.SelectedValue & "'," & _
                         " CodIncoterm = '" & ddlIncoterm.SelectedValue & "'," & _
                         " CodCondicaoPagamento = " & ddlCondicaoPagamento.SelectedValue & "," & _
                         " LastUser = '" & Session("sNM_USERNICKNAME").ToString & "'," & _
                         " LastUpdated = " & nLastUpdated & "" & _
                         " WHERE CodigoDaHolding = '" & lblHolding.ToolTip & "'" & _
                         " AND CodigoDaCia = '" & ddlCompanhia.SelectedValue & "'" & _
                         " AND CodigoDoEstabelecimento = '" & ddlEstabelecimento.SelectedValue & "'" & _
                         " AND Fatura = '" & UCase(Trim(txtFatura.Text)) & "';"

                Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
                theSqlCommand.CommandText = theQueryString

                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()
                theSqlDataReader.Close()
                EditRecord = True
            Catch ex As Exception
                'theSqlDataReader.Close()
                lblMsg.Text = "Msg DB: " & ex.Message
            End Try

        End Using

    End Function


    Private Function VerifyData(ByVal strConn As String) As Boolean

        VerifyData = False

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            Dim theQueryString As String
            theQueryString = "SELECT Fatura" & _
                     " FROM custofaturadetail" & _
                     " WHERE CodigoDaHolding = '" & lblHolding.ToolTip & "'" & _
                     " AND CodigoDaCia = '" & ddlCompanhia.SelectedValue & "'" & _
                     " AND CodigoDoEstabelecimento = '" & ddlEstabelecimento.SelectedValue & "'" & _
                     " AND Fatura = '" & UCase(Trim(txtFatura.Text)) & "';"

            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString

            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()

                If (theSqlDataReader.Read()) Then
                    VerifyData = True
                    lblMsg.Text = "Exclusão não permitida, existe relação da Fatura " & txtFatura.Text & " com Items."
                End If
                theSqlDataReader.Close()
            Catch ex As Exception
                VerifyData = True
                'theSqlDataReader.Connection.Close()
                lblMsg.Text = "Msg DB: " & ex.Message
            End Try

        End Using

    End Function

    Private Function DeleteRecord(ByVal strConn As String) As Boolean

        DeleteRecord = False

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            Dim theQueryString As String
            theQueryString = "DELETE FROM custofaturaheader" & _
                         " WHERE CodigoDaHolding = '" & lblHolding.ToolTip & "'" & _
                         " AND CodigoDaCia = '" & ddlCompanhia.SelectedValue & "'" & _
                         " AND CodigoDoEstabelecimento = '" & ddlEstabelecimento.SelectedValue & "'" & _
                         " AND Fatura = '" & UCase(Trim(txtFatura.Text)) & "';"

            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString
            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()
                theSqlDataReader.Close()
                DeleteRecord = True
            Catch ex As Exception
                'theSqlDataReader.Close()
                lblMsg.Text = "Msg DB: " & ex.Message
            End Try

        End Using

    End Function

    Protected Sub btnVoltar_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnVoltar.Click

        Response.Redirect("CustoFatura.aspx")

    End Sub

    Public Function FormatMediumDate(ByVal DateValue)

        Dim strYYYY
        Dim strMM
        Dim strDD

        'Strip the Year
        strYYYY = CStr(DatePart("yyyy", DateValue))

        'Strip the Month	
        strMM = CStr(DatePart("m", DateValue))

        'See if month is a single digit if so display it as 01, 02, 03, etc
        If Len(strMM) = 1 Then strMM = "0" & strMM

        'Strip the Date	
        strDD = CStr(DatePart("d", DateValue))

        'See if the Day is a single digit day if so diaply it as 01, 02, 03, etc
        If Len(strDD) = 1 Then strDD = "0" & strDD

        'return the date formatted as DD/MM/YYY	
        FormatMediumDate = strYYYY & "/" & strMM & "/" & strDD

        'you could return the date many ways just retype the above string
        'FormatMediumDate = strDD & "-" & strMM & "-" & strYYYY
        'FormatMediumDate = strMM & "/" & strDD & "/" & strYYYY
        'FormatMediumDate = strYYYY & "-" & strMM & "-" & strMM

    End Function

    Protected Sub btnItem_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnItem.Click

        'Session.Add("strOperacao", "A") ' Alterar

        Session.Add("strCodigoDaHolding", lblHolding.ToolTip)
        Session.Add("strCodigoDaCia", HttpUtility.HtmlDecode(Session("strCodigoDaCia").ToString))
        Session.Add("strCodigoDoEstabelecimento", HttpUtility.HtmlDecode(Session("strCodigoDoEstabelecimento").ToString))
        Session.Add("strFatura", HttpUtility.HtmlDecode(txtFatura.Text))

        Response.Redirect("CustoFaturaLinha.aspx")

    End Sub

End Class
